﻿CREATE TRIGGER tr_Loan_Verification_Insert
ON dbo.t_Loan_Verification
FOR INSERT
AS

IF @@ROWCOUNT <> 1 RETURN

DECLARE @VID int,@LoanID int,@Checked bit,@IsCustom bit
SELECT @LoanID = LoanID, @VID = ISNULL(VerificationID,0),@Checked = Checked,@IsCustom = IsCustom FROM inserted

IF @IsCustom = 0 RETURN

IF @VID = 0 BEGIN --============================================================

	SELECT @VID = ISNULL(MIN(VerificationID),0)-1 FROM t_Loan_Verification
		WHERE LoanID IN (SELECT LoanID FROM inserted) AND VerificationID < 0

	UPDATE t_Loan_Verification
	SET VerificationID = @VID
	WHERE LoanID IN (SELECT LoanID FROM inserted) AND VerificationID = 0

END --===========================================================================

DECLARE @MenuID int SELECT @MenuID = ISNULL(MenuID,0) FROM inserted
IF @MenuID = 0 BEGIN --==========================================================

	SELECT @MenuID = l.StatusID FROM t_VerificationSmart l	INNER JOIN inserted i ON l.LoanID = i.LoanID

	UPDATE t_Loan_Verification
	SET MenuID = @MenuID
	FROM t_Loan_Verification lv
	INNER JOIN inserted i ON lv.LoanID = i.LoanID AND
													 lv.VerificationID = @VID

END --===========================================================================

DECLARE @ParentID int SELECT @ParentID = ISNULL(ParentID,0) FROM inserted
IF @ParentID = 0 BEGIN --========================================================

	SELECT @ParentID = ID FROM t_Verification WHERE ParentID = 0 AND MenuID = @MenuID ORDER BY Pos DESC

	UPDATE t_Loan_Verification
	SET ParentID = @ParentID
	FROM t_Loan_Verification lv
	INNER JOIN inserted i ON lv.LoanID = i.LoanID AND
													 lv.VerificationID = @VID

END --===========================================================================
DECLARE @Pos int SELECT @Pos = ISNULL(Pos,0) FROM inserted
IF @Pos = 0 BEGIN --=============================================================

SELECT @Pos = ISNULL(MAX(TabIndex),0) + 1 FROM v_Loan_Verification
WHERE  LoanID IN (SELECT LoanID FROM inserted) AND (ParentID = @ParentID)

	UPDATE t_Loan_Verification
	SET Pos = @Pos
	FROM t_Loan_Verification lv
	INNER JOIN inserted i ON lv.LoanID = i.LoanID AND
													 lv.VerificationID = @VID

END --===========================================================================
--History
IF EXISTS(SELECT * FROM t_Loan_Verification WHERE LoanID = @LoanID AND VerificationID = @VID AND IsCustom = 1)
	INSERT INTO t_Verification_CustomHistory	SELECT Lable,@MenuID FROM inserted

-- Set Owner
UPDATE t_Loan_Verification SET Owner = dbo.UserName() WHERE LoanID = @LoanID AND VerificationID = @VID AND @VID < 0

